package hip.util.poi;

import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;
import org.apache.poi.hssf.eventusermodel.*;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author qiujl
 * @date 2016-3-2
 * @describe 通用poi导入导出接口服务
 */

public abstract class HxlsAbstract implements HSSFListener {
    private static Logger logger = LoggerFactory.getLogger(HxlsAbstract.class);
    private int minColumns;
    private POIFSFileSystem fs;
    private PrintStream output;

    private int lastRowNumber;
    private int lastColumnNumber;

    /**
     * Should we output the formula, or the value it has?
     */
    private boolean outputFormulaValues = true;

    /**
     * For parsing Formulas
     */
    private SheetRecordCollectingListener workbookBuildingListener;
    private HSSFWorkbook stubWorkbook;

    // Records we pick up as we process
    private SSTRecord sstRecord;
    private FormatTrackingHSSFListener formatListener;

    /**
     * So we known which sheet we're on
     */
    private int sheetIndex = -1;
    private BoundSheetRecord[] orderedBSRs;
    @SuppressWarnings("rawtypes")
    private ArrayList boundSheetRecords = new ArrayList();

    // For handling formulas with string results
    private int nextRow;
    private int nextColumn;
    private boolean outputNextStringRecord;
    private int curRow;
    private List<String> rowlist;

    @SuppressWarnings("unused")
    private String sheetName;

    public HxlsAbstract(POIFSFileSystem fs) throws SQLException {
        this.fs = fs;
        this.output = System.out;
        this.minColumns = -1;
        this.curRow = 0;
        this.rowlist = new ArrayList<String>();
    }

    public HxlsAbstract(String filename) throws IOException, FileNotFoundException, SQLException {
        this(new POIFSFileSystem(new FileInputStream(filename)));
    }

    // excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型,rowlist存储了行数据
    public abstract void optRows(int sheetIndex, int curRow, List<String> rowlist) throws Exception;

    /**
     * 遍历 excel 文件
     */
    public void process() throws IOException {
        MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
        formatListener = new FormatTrackingHSSFListener(listener);

        HSSFEventFactory factory = new HSSFEventFactory();
        HSSFRequest request = new HSSFRequest();

        if (outputFormulaValues) {
            request.addListenerForAllRecords(formatListener);
        } else {
            workbookBuildingListener = new SheetRecordCollectingListener(formatListener);
            request.addListenerForAllRecords(workbookBuildingListener);
        }

        factory.processWorkbookEvents(request, fs);
    }

    /**
     * HSSFListener 监听方法，处理 Record
     */
    @SuppressWarnings("unchecked")
    public void processRecord(Record record) {
        int thisRow = -1;
        int thisColumn = -1;
        String thisStr = null;
        String value = null;

        switch (record.getSid()) {
            //开始解析Sheet的信息，记录sheet，这儿会把所有的sheet都顺序打印出来，如果有多个sheet的话，可以顺序记入到一个List里
            case BoundSheetRecord.sid:
                BoundSheetRecord bsr = (BoundSheetRecord) record;
                boundSheetRecords.add(bsr);
                logger.debug("New sheet named: " + bsr.getSheetname());
                break;
            case BOFRecord.sid:
                BOFRecord br = (BOFRecord) record;
                if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
                    logger.debug("开始解析sheet页面内容...");
                    // Create sub workbook if required
                    if (workbookBuildingListener != null && stubWorkbook == null) {
                        stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
                    }

                    // Works by ordering the BSRs by the location of
                    // their BOFRecords, and then knowing that we
                    // process BOFRecords in byte offset order
                    sheetIndex++;
                    if (orderedBSRs == null) {
                        orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);
                    }
                    sheetName = orderedBSRs[sheetIndex].getSheetname();
                }
                break;
            // SSTRecords store a array of unique strings used in Excel.
            case SSTRecord.sid:
                sstRecord = (SSTRecord) record;
                for (int k = 0; k < sstRecord.getNumUniqueStrings(); k++) {
                    logger.debug("String table value " + k + " = " + sstRecord.getString(k));
                }
                break;
            //空白记录的信息
            case BlankRecord.sid:
                BlankRecord brec = (BlankRecord) record;

                thisRow = brec.getRow();
                thisColumn = brec.getColumn();
                thisStr = "";
                logger.debug("空白记录:　行：" + thisRow + ", 列：" + thisColumn);
                rowlist.add(thisColumn, " ");
                break;
            //解析boolean错误信息
            case BoolErrRecord.sid:
                BoolErrRecord berec = (BoolErrRecord) record;

                thisRow = berec.getRow();
                thisColumn = berec.getColumn();
                thisStr = "";
                if (berec.isBoolean()) {
                    logger.debug("Boolean:" + berec.getBooleanValue() + ", 行：" + thisRow + ", 列：" + thisColumn);
                }
                if (berec.isError()) {
                    logger.debug("Error:" + berec.getErrorValue() + ", 行：" + thisRow + ", 列：" + thisColumn);
                }
                break;
            //数式
            case FormulaRecord.sid:
                FormulaRecord frec = (FormulaRecord) record;

                thisRow = frec.getRow();
                thisColumn = frec.getColumn();

                if (outputFormulaValues) {
                    if (Double.isNaN(frec.getValue())) {
                        // Formula result is a string
                        // This is stored in the next record
                        outputNextStringRecord = true;
                        nextRow = frec.getRow();
                        nextColumn = frec.getColumn();
                    } else {
                        thisStr = formatListener.formatNumberDateCell(frec);
                    }
                } else {
                    thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
                }
                logger.debug("数式。　行：" + thisRow + ", 列：" + thisColumn);
                break;
            //Supports the STRING record structure
            case StringRecord.sid:
                if (outputNextStringRecord) {
                    // String for formula
                    StringRecord srec = (StringRecord) record;
                    thisStr = srec.getString();
                    thisRow = nextRow;
                    thisColumn = nextColumn;
                    outputNextStringRecord = false;
                }
                logger.debug("字符串。　行：" + thisRow + ", 列：" + thisColumn);
                break;
            //read only support for strings stored directly in the cell.. Don't use this (except to read), use LabelSST instead
            case LabelRecord.sid:
                LabelRecord lrec = (LabelRecord) record;

                curRow = thisRow = lrec.getRow();
                thisColumn = lrec.getColumn();
                value = lrec.getValue().trim();
                value = value.equals("") ? " " : value;
                this.rowlist.add(thisColumn, value);
                break;
            // 发现字符串类型，这儿要取字符串的值的话，根据其index去字符串表里读取
            case LabelSSTRecord.sid:
                LabelSSTRecord lsrec = (LabelSSTRecord) record;

                curRow = thisRow = lsrec.getRow();
                thisColumn = lsrec.getColumn();
                if (sstRecord == null) {
                    rowlist.add(thisColumn, " ");
                } else {
                    value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();
                    value = value.equals("") ? " " : value;
                    rowlist.add(thisColumn, value);
                }
                logger.debug("文字列:" + value + ",　行：" + thisRow + ", 列：" + thisColumn);
                break;
            //批注解析
            case NoteRecord.sid:
                NoteRecord nrec = (NoteRecord) record;

                thisRow = nrec.getRow();
                thisColumn = nrec.getColumn();
                // TODO: Find object to match nrec.getShapeId()
                thisStr = '"' + "(TODO)" + '"';
                logger.debug("批注:" + thisStr + ",　行：" + thisRow + ", 列：" + thisColumn);
                break;
            //发现数字类型的cell，因为数字和日期都是用这个格式，所以下面一定要判断是不是日期格式，另外默认的数字也会被视为日期格式，所以如果是数字的话，一定要明确指定格式！！！！！！！
            case NumberRecord.sid:
                NumberRecord numrec = (NumberRecord) record;

                curRow = thisRow = numrec.getRow();
                thisColumn = numrec.getColumn();
                value = formatListener.formatNumberDateCell(numrec).trim();
                value = value.equals("") ? " " : value;
                // Format
                rowlist.add(thisColumn, value);
                logger.debug("数字:" + value + ",　行：" + thisRow + ", 列：" + thisColumn);
                break;
            //We support this in READ-ONLY mode. HSSF converts these to NUMBER records
            case RKRecord.sid:
                RKRecord rkrec = (RKRecord) record;

                thisRow = rkrec.getRow();
                thisColumn = rkrec.getColumn();
                thisStr = '"' + "(TODO)" + '"';
                logger.debug("RKRecord:" + thisStr + ",　行：" + thisRow + ", 列：" + thisColumn);
                break;
            default:
                break;
        }

        // 遇到新行的操作
        if (thisRow != -1 && thisRow != lastRowNumber) {
            lastColumnNumber = -1;
        }

        // 空值的操作
        if (record instanceof MissingCellDummyRecord) {
            MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
            curRow = thisRow = mc.getRow();
            thisColumn = mc.getColumn();
            logger.debug("空值:" + thisStr + ",　行：" + thisRow + ", 列：" + thisColumn);
            rowlist.add(thisColumn, " ");
        }

        // 如果遇到能打印的东西，在这里打印
        if (thisStr != null) {
            if (thisColumn > 0) {
                output.print(',');
            }
            output.print(thisStr);
        }

        // 更新行和列的值
        if (thisRow > -1) lastRowNumber = thisRow;
        if (thisColumn > -1) lastColumnNumber = thisColumn;

        // 行结束时的操作
        if (record instanceof LastCellOfRowDummyRecord) {
            if (minColumns > 0) {
                // 列值重新置空
                if (lastColumnNumber == -1) {
                    lastColumnNumber = 0;
                }
            }
            // 行结束时， 调用 optRows() 方法
            lastColumnNumber = -1;
            try {
                optRows(sheetIndex, curRow, rowlist);
            } catch (Exception e) {
                e.printStackTrace();
            }
            rowlist.clear();
        }
    }
}
